﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TravelSite.Entities;
using Dapper;

namespace TravelSite.DAL.Repositories
{
    public class EmailSettingRepository : BaseRepository<EmailSetting>
    {
        protected override string TableName
        {
            get { return "[EmailSetting]"; }
        }

        public override IEnumerable<EmailSetting> FindAll()
        {
            string strSQL = @"  select EmailSettingId, EmailSettingName, EmailFrom, ServerHost, ServerPort, UserName, Password, UseSSL, IsDefault,
                                convert(bit, case 
                                    when exists(select 1 from [User] (nolock) as u where vw.EmailSettingId = u.EmailSettingId) then 0
                                else 1 end) as CanDelete
                    from [EmailSetting] (nolock) as vw 
                    order by EmailSettingName ";

            IEnumerable<EmailSetting> result = Connection.Query<EmailSetting>(strSQL);
            return result;
        }

        public override EmailSetting Find(int id)
        {
            EmailSetting result = Connection.Query<EmailSetting>(
                @"  select EmailSettingId, EmailSettingName, EmailFrom, ServerHost, ServerPort, UserName, Password, UseSSL, IsDefault
                    from [EmailSetting] (nolock) as vw 
                    where EmailSettingId = @EmailSettingId
                    order by EmailSettingName ", new { EmailSettingId = id }).SingleOrDefault();
            return result;
        }

        public EmailSetting Find()
        {
            EmailSetting result = Connection.Query<EmailSetting>(
                @"  select EmailSettingId, EmailSettingName, EmailFrom, ServerHost, ServerPort, UserName, Password, UseSSL, IsDefault
                    from [EmailSetting] (nolock) as vw 
                    where IsDefault = 1 ").SingleOrDefault();
            return result;
        }

        public override void Insert(EmailSetting item)
        {
            if (item.IsDefault)
                Connection.Execute(@"update EmailSetting set IsDefault = 0");

            Connection.Execute(
                @"insert into [EmailSetting] 
                (EmailSettingName, EmailFrom, ServerHost, ServerPort, UserName, Password, UseSSL, IsDefault)
                values (@EmailSettingName, @EmailFrom, @ServerHost, @ServerPort, @UserName, @Password, @UseSSL, @IsDefault)", item);
        }

        public override void Update(EmailSetting item)
        {
            if (item.IsDefault)
                Connection.Execute(@"update EmailSetting set IsDefault = 0");

            Connection.Execute(
                @"update EmailSetting
                    set EmailSettingName = @EmailSettingName, EmailFrom = @EmailFrom, ServerHost = @ServerHost, ServerPort = @ServerPort, UserName = @UserName, Password = @Password, UseSSL = @UseSSL, IsDefault = @IsDefault
                    where (EmailSettingId = @EmailSettingId)", item);
        }

        public void SetDefault(int id)
        {
            Connection.Execute(
                @"update EmailSetting
                    set IsDefault = case when EmailSettingId = @EmailSettingId then 1 else 0 end
                    ", new { EmailSettingId = id });
        }

        public override void Delete(int id)
        {
            Connection.Execute(
                @"delete 
                    from EmailSetting
                    where (EmailSettingId = @OptionId)", new { OptionId = id });
        }

        public IEnumerable<EmailSetting> EmailSettingsCombo(bool includeAll, bool includeEmpty)
        {
            string strSQL = "";
            if (includeAll)
                strSQL = @"select -1 as EmailSettingId, 'ALL' as EmailSettingName, 0 as SortIndex union all ";
            else if (includeEmpty)
                strSQL = @"select -1 as EmailSettingId, '(Select EmailSetting)' as EmailSettingName, 0 as SortIndex union all ";

            strSQL += @"select EmailSettingId, EmailSettingName, 1 as SortIndex from [EmailSetting] (nolock) order by SortIndex, EmailSettingName";
            return Connection.Query<EmailSetting>(strSQL);
        }

    }
}
